package com.gxuwz.weather.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

public class CityDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "weather.db";
    private static final int DATABASE_VERSION = 2;

    public static final String TABLE_CITIES = "cities";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_CITY_NAME = "city_name";
    public static final String COLUMN_ADD_TIME = "add_time";

    public static final String TABLE_ALL_CITIES = "all_cities";
    public static final String COLUMN_AREA_CODE = "areaCode";
    public static final String COLUMN_AREA_ID = "areaId";
    public static final String COLUMN_AREA_EN = "areaEn";
    public static final String COLUMN_AREA_CN = "areaCn";
    public static final String COLUMN_CITY_EN = "cityEn";
    public static final String COLUMN_CITY_CN = "cityCn";
    public static final String COLUMN_PROV_EN = "provEn";
    public static final String COLUMN_PROV_CN = "provCn";

    private static final String CREATE_TABLE_CITIES = 
            "CREATE TABLE " + TABLE_CITIES + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_CITY_NAME + " TEXT NOT NULL UNIQUE, " +
            COLUMN_ADD_TIME + " INTEGER NOT NULL" +
            ")";

    private static final String CREATE_TABLE_ALL_CITIES =
            "CREATE TABLE " + TABLE_ALL_CITIES + " (" +
                    COLUMN_AREA_CODE + " TEXT, " +
                    COLUMN_AREA_ID + " TEXT, " +
                    COLUMN_AREA_EN + " TEXT, " +
                    COLUMN_AREA_CN + " TEXT, " +
                    COLUMN_CITY_EN + " TEXT, " +
                    COLUMN_CITY_CN + " TEXT, " +
                    COLUMN_PROV_EN + " TEXT, " +
                    COLUMN_PROV_CN + " TEXT" +
                    ")";

    public CityDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_CITIES);
        db.execSQL(CREATE_TABLE_ALL_CITIES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITIES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ALL_CITIES);
        onCreate(db);
    }

    public void importAllCitiesFromAssets(Context context) {
        SQLiteDatabase db = getWritableDatabase();
        try {
            Log.d("CityDatabaseHelper", "开始读取all_cities.csv文件...");
            InputStream is = context.getAssets().open("all_cities.csv");
            BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));
            String line;
            boolean isFirstLine = true;
            int importCount = 0;
            db.beginTransaction();
            while ((line = reader.readLine()) != null) {
                if (isFirstLine) {
                    isFirstLine = false;
                    Log.d("CityDatabaseHelper", "跳过标题行: " + line);
                    continue;
                }
                String[] fields = line.split(",");
                if (fields.length < 8) {
                    Log.w("CityDatabaseHelper", "跳过格式错误的行: " + line);
                    continue;
                }
                ContentValues values = new ContentValues();
                values.put("areaCode", fields[0]);
                values.put("areaId", fields[1]);
                values.put("areaEn", fields[2]);
                values.put("areaCn", fields[3]);
                values.put("cityEn", fields[4]);
                values.put("cityCn", fields[5]);
                values.put("provEn", fields[6]);
                values.put("provCn", fields[7]);
                long result = db.insert("all_cities", null, values);
                if (result != -1) {
                    importCount++;
                }
            }
            db.setTransactionSuccessful();
            db.endTransaction();
            reader.close();
            Log.d("CityDatabaseHelper", "成功导入 " + importCount + " 条城市数据");
        } catch (Exception e) {
            Log.e("CityDatabaseHelper", "导入城市数据失败", e);
            e.printStackTrace();
        }
    }
} 